<?php

class Model_Account
{

    public static function login ($params)
    {
        try
        {
            $db = Core_Global::getDatabase();
            
            $stmt = $db->query('SELECT * FROM Account WHERE Email  = ? AND Password = ?', 
                    array(
                            $params['email'],
                            md5($params['password'])
                    ));
            
            return $stmt->fetch();
        }
        catch (Zend_Db_Exception $e)
        {
            throw new Zend_Db_Exception($e);
        }
    }

    public static function get ($params)
    {
        try
        {
            $db = Core_Global::getDatabase();
            
            $stmt = $db->prepare(
                    "SELECT SQL_CALC_FOUND_ROWS Account.*, Card.ExpirationDate AS CardExpirationDate, Card.CreateDate AS CardCreateDate
                                FROM Account
                                LEFT JOIN Card ON (Account.CardID = Card.CardID)
                                WHERE Account.AccountID = :p_AccountID AND Account.Status = :p_Status");
            
            $stmt->bindParam(':p_AccountID', $params['AccountID'], PDO::PARAM_INT);
            $stmt->bindParam(':p_Status', $params['Status'], PDO::PARAM_INT);
            
            $stmt->execute();
            
            return $stmt->fetch();
        }
        catch (Zend_Db_Exception $e)
        {
            throw new Zend_Db_Exception($e);
        }
    }

    public static function getAll ($params, &$count)
    {
        try
        {
            $db = Core_Global::getDatabase();
            
            $params['Offset'] = ($params['Page'] - 1) * $params['Limit'];
            
            $stmt = $db->prepare(
                    "SELECT SQL_CALC_FOUND_ROWS Account.*, Card.ExpirationDate AS CardExpirationDate, Card.CreateDate AS CardCreateDate 
                                FROM Account 
                                LEFT JOIN Card ON (Account.CardID = Card.CardID) 
                                WHERE Account.Type IN (:p_Type) AND Account.Status IN (:p_Status) LIMIT :p_Offset, :p_Limit");
            
            $stmt->bindParam(':p_Type', $params['Type'], PDO::PARAM_STR);
            $stmt->bindParam(':p_Status', $params['Status'], PDO::PARAM_STR);
            $stmt->bindParam(':p_Limit', $params['Limit'], PDO::PARAM_INT);
            $stmt->bindParam(':p_Offset', $params['Offset'], PDO::PARAM_INT);
            
            $stmt->execute();
            
            $data = $stmt->fetchAll();
            
            $stmt = $db->query('SELECT FOUND_ROWS()');
            
            $count = $stmt->fetchColumn();
            
            return $data;
        }
        catch (Zend_Db_Exception $e)
        {
            throw new Zend_Db_Exception($e);
        }
    }

    public static function create ($params)
    {
        try
        {
            $db = Core_Global::getDatabase();
            
            $stmt = $db->query('INSERT INTO Account VALUES(?,?,?,?,?,?,?,?,?,?,?)', 
                    array(
                            null,
                            $params['CardID'],
                            $params['Name'],
                            $params['Email'],
                            md5($params['Password']),
                            $params['DOB'],
                            $params['IDNo'],
                            $params['Avatar'],
                            $params['Type'],
                            microtime(true),
                            1
                    ));
            
            return $db->lastInsertId();
        }
        catch (Zend_Db_Exception $e)
        {
            throw new Zend_Db_Exception($e);
        }
    }

    public static function edit ($params)
    {
        try
        {
            $db = Core_Global::getDatabase();
            
            $sql = " UPDATE Account SET
                     Name = :p_Name, Email = :p_Email ,DOB = :p_DOB, IDNo = :p_IDNo";
            
            if ($params['Password'])
            {
                $sql .= " ,Password = :p_Password";
            }
            
            $sql .= " WHERE AccountID = :p_AccountID";
            
            $stmt = $db->prepare($sql);
            
            $stmt->bindValue(':p_Name', $params['Name']);
            $stmt->bindValue(':p_Email', $params['Email']);
            $stmt->bindValue(':p_DOB', $params['DOB']);
            $stmt->bindValue(':p_IDNo', $params['IDNo']);
            $stmt->bindValue(':p_AccountID', $params['AccountID']);
            
            if ($params['Password'])
            {
                $stmt->bindValue(':p_Password', $params['Password']);
            }
            
            return $stmt->execute();
        }
        catch (Zend_Db_Exception $e)
        {
            throw new Zend_Db_Exception($e);
        }
    }
}
